package com.zhang.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

class Table {
    String tableName;
    List<String> columns;
    String tableComment;

    public Table(String tableName) {
        this.tableName = tableName;
        this.columns = new ArrayList<>();
        this.tableComment = "";
    }

    public void addColumn(String column) {
        this.columns.add(column);
    }

    public void setTableComment(String comment) {
        this.tableComment = comment;
    }

    public String getCreateTableStatement() {
        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE ").append(tableName).append(" (\n");
        for (int i = 0; i < columns.size(); i++) {
            sb.append("  ").append(columns.get(i));
            if (i < columns.size() - 1) {
                sb.append(",");
            }
            sb.append("\n");
        }
        sb.append(")");
        if (!tableComment.isEmpty()) {
            sb.append(" COMMENT = '").append(tableComment).append("'");
        }
        sb.append(";");
        return sb.toString();
    }
}

public class TableParser {
    public static void main(String[] args) {
        String filePath = "D:\\桌面\\导出数据_20250320185152.xls";
        String outputFilePath = "D:\\桌面\\output.sql"; // 输出的 SQL 文件路径
        Map<String, Table> tables = new HashMap<>();

        try (FileInputStream file = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(file);
             FileWriter writer = new FileWriter(outputFilePath)) {

            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = sheet.getLastRowNum();

            for (int i = 1; i <= rowCount; i++) {
                Row row = sheet.getRow(i);
                String tableName = getCellValueAsString(row.getCell(2));
                String columnName = getCellValueAsString(row.getCell(3));
                String isNullable = getCellValueAsString(row.getCell(6));
                String columnType = getCellValueAsString(row.getCell(15));
                String extra = getCellValueAsString(row.getCell(17));
                String columnComment = getCellValueAsString(row.getCell(19));
                String tableComment = getCellValueAsString(row.getCell(41));

                Table table = tables.computeIfAbsent(tableName, Table::new);
                StringBuilder columnDefinition = new StringBuilder();
                columnDefinition.append("`").append(columnName).append("` ").append(columnType);
                if ("NO".equals(isNullable)) {
                    columnDefinition.append(" NOT NULL");
                }
                if (extra.contains("auto_increment")) {
                    columnDefinition.append(" PRIMARY KEY AUTO_INCREMENT");
                } else if (!extra.isEmpty()) {
                    columnDefinition.append(" ").append(extra);
                }
                if (!columnComment.isEmpty()) {
                    columnDefinition.append(" COMMENT '").append(columnComment).append("'");
                }
                table.addColumn(columnDefinition.toString());
                table.setTableComment(tableComment);
            }

            for (Table table : tables.values()) {
                String createTableStatement = table.getCreateTableStatement();
                writer.write(createTableStatement);
                writer.write("\n\n"); // 添加换行，使每个语句之间有间隔
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf((int) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
}